前言

有時候在寫 SQL 語法時為了方便，可能會去建立臨時表存一些資料，以利後面需要用到的時候可以拿來使用。也就是利用 CREATE TEMPORARY TABLE table_table ........ ，我們會稱它叫臨時表。
另外有時候在看一些優化效能的網站時，也會看到有些語法會去建立臨時表??
這兩種臨時表有什麼不一樣，在這裡做一個整理。
這裡主要介紹一些臨時表的概念，至於怎麼建立，相信這塊網路上已經非常多了，就不另外說明。

外部臨時表

首先臨時表之一的就是外部臨時表，也就是當我們使用 CREATE TEMPORARY TABLE table_table ........去建立的，此種表只能在當前的 session 使用，也就是說

• 當 session 斷掉此臨時表就會自動 drop 掉，當然我們也可以自己 drop 這個臨時表
• 其他 session 可以用相同名稱的臨時表

★ 外部臨時表存放位置

這裡只要先知道外部臨時表是放在 disk 上，後面會再探討。

內部臨時表

內部臨時表是由 MySQL 自己創建的，用來進行性能優化用的，對用戶來說是不可見的，只有透過 explain 才會看到，藉以了解是否有用到內部臨時表來協助完成某些事情。
有很多動作都會利用到內部臨時表來進行優化，例如

• UNION
• DISTINCT
• view
• derived tables
• CTE
• ORDER BY , GROUP BY

如果有用到內部臨時表，會在 explain 的 extra 欄位看到，如下圖

內部臨時表又分成兩種

1. 放在 memory 中，HEAP 臨時表，不需 I/O 操作
2. 放在 disk，當中間結果集較大的時候，例如超過參數 MAX_HEAP_TABLE_SIZE 設定的大小，就會放到 disk

★ 內部臨時表存放位置

如同上面提到的，有分 memory 和 disk

★ 內部臨時表相關參數

只要 MySQL Server 有建立內部臨時表(不管是建在 memory 還是 disk 上) Created_tmp_tables 這個 status variable 就會增加。
如果是建立到 disk 上，那麼 Created_tmp_disk_tables 這個參數就會增加。
藉由這兩個可以去觀察內部臨時表到底建立到 memory 還是 disk 上。

show status like '%Created_tmp_disk_tables%';
show status like '%Created_tmp_tables%';

關於臨時表空間 temporary tablespace

這裡之所以會特別提到 temporary tablespace 是因為前面有談到關於臨時表是建立在 disk 上還是 memory 上，對於資料庫來說， I/O 絕對是效能重要的瓶頸，所以我們有必要了解這一塊。
在 InnoDB 中 temporary tablespace 有兩種

1. session temporary tablespaces
2. global temporary tablespace.

這裡我們主要討論的是 session temporary tablespaces。

session temporary tablespaces

這裡主要存放了以下兩種
1.外部臨時表：使用者自己建的 create temporary table
2.內部臨時表: 由 optimizer 建立的，但是前提是這個內部臨時表是 on disk

這裡以 mysql8.0.16 為例，session temporary tablespaces 在 mysql 8.0.16 之後會在 #innodb_temp/ 這個目錄下。從下圖可以看到有 10 個檔案，這10個檔案組成一個 pool。

• 這個 pool 是在 server start 就建立的，也就是說 server 一開始就有這10個檔案，也就是 pool
• 這個 pool 不會 shrinks，且有必要的會會自動 add
• 這些 ibt 副檔名的就是 session tamporary tablespace
• 每一次重啟 server ，這些 .ibt 檔案就會重建
• 只要 session 結束，這些 session tamporary tables 就會 truncate 且還給 pool

小結

這篇文章大略介紹了內部外部臨時表的差異，主要有鑑於自己在查資料時，大多網站只有寫臨時表，但其實還是有所區分，所以在這裡幫助自己也希望幫助到大家有稍微了解到這兩者的差異。
此外跟此主題相關的還有

◎ Materialization:　

這是 MySQL 的特性，主要是跟優化 subquery, cte 相關，當然也跟內部臨時表密不可分，大家有興趣可以上網查詢一下，下次也會另外寫一篇介紹此特性。

資料庫知識相當廣泛，文中若有不正確的地方，也煩請各位大神不吝指教，謝謝

參考網站
CREATE TEMPORARY TABLE Statement
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
Internal Temporary Table Use in MySQL
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
Temporary Tablespaces
https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html
Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization
https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html
Optimizing Subqueries with Materialization
https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html